﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace L.EntityFramework
{
    public class BaseRepository<TC> : IBaseRepository<TC> where TC : BaseContext, new()
    {

        #region 构造

        TC tcObject;
        public BaseRepository()
        {
            if (tcObject == null)
            {
                tcObject = new TC();
            }
        }

        #endregion

        #region 删除

        /// <summary>
        /// 通过实体模型删除数据库中数据
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Delete<T>(T t) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (t == null) { return 0; }
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Deleted;
                return tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过实体模型删除数据库中数据
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> DeleteAsync<T>(T t) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (t == null) { return 0; }
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Deleted;
                return await tc.SaveChangesAsync();
            }
        }

        /// <summary>
        /// 通过实体模型集合删除数据库中数据
        /// 返回受影响行数
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Delete<T>(IEnumerable<T> t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Deleted;
                }
                return tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过实体模型集合删除数据库中数据
        /// 返回受影响行数
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> DeleteAsync<T>(IEnumerable<T> t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Deleted;
                }
                return await tc.SaveChangesAsync();
            }
        }

        /// <summary>
        /// 通过Lambda表达式删除数据库中数据
        /// 只可用于小规模数据删除
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Delete<T>(Expression<Func<T, bool>> expression) where T : BaseEntity
        {
            if (expression == null)
            {
                throw new ArgumentNullException("expression");
            }
            using (TC tc = new TC())
            {
                IEnumerable<T> t = tc.Set<T>().Where(expression);
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Deleted;
                }
                return tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过Lambda表达式删除数据库中数据
        /// 只可用于小规模数据删除
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> DeleteAsync<T>(Expression<Func<T, bool>> expression) where T : BaseEntity
        {
            return await Task.Run(() =>
            {
                if (expression == null)
                {
                    throw new ArgumentNullException("expression");
                }
                using (TC tc = new TC())
                {
                    IEnumerable<T> t = tc.Set<T>().Where(expression);
                    foreach (var item in t)
                    {
                        tc.Entry<T>(item).State = System.Data.Entity.EntityState.Deleted;
                    }
                    return tc.SaveChangesAsync();
                }
            });
        }
        
        #endregion

        #region 新增

        /// <summary>
        /// 通过实体模型向数据库中添加单条记录
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Insert<T>(T t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Added;
                return tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过实体模型向数据库中添加单条记录
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> InsertAsync<T>(T t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Added;
                return await tc.SaveChangesAsync();
            }
        }


        /// <summary>
        /// 通过实模型集合向数据库中添加多条记录
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Insert<T>(IEnumerable<T> t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Added;
                }
                return tc.SaveChanges();
            }
        }


        /// <summary>
        /// 通过实模型集合向数据库中添加多条记录
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> InsertAsync<T>(IEnumerable<T> t) where T : BaseEntity
        {
            return await Task.Run(() =>
             {
                 if (t == null) { return Task.FromResult<int>(0); }
                 using (TC tc = new TC())
                 {
                     foreach (var item in t)
                     {
                         tc.Entry<T>(item).State = System.Data.Entity.EntityState.Added;
                     }
                     return tc.SaveChangesAsync();
                 }
             });
        }

        /// <summary>
        /// 执行SQL语句向数据库添加单条或多条数据
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public int Insert<T>(string sql, MySqlParameter[] parameters = null) where T : BaseEntity
        {
            return this.ExecuteSql(sql, parameters);
        }

        /// <summary>
        /// 执行SQL语句向数据库添加单条或多条数据
        /// Lv
        /// 0.1
        /// 2018/02/10
        /// 返回受影响行数
        /// </summary>
        public async Task<int> InsertAsync<T>(string sql, MySqlParameter[] parameters = null) where T : BaseEntity
        {
            return await this.ExecuteSqlAsync(sql, parameters);
        }

        [Obsolete("未实现方法，始终返回0 ")]
        public int InsertAdoptSql<T>(T t) where T : BaseEntity
        {
            //string connectionString = tcObject.Database.Connection.ConnectionString;
            return 0;
        }
        #endregion

        #region 查询

        /// <summary>
        /// 通过Lambda或查询单条或多条记录
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public IQueryable<T> Search<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            if (expression == null)
            {
                return tcObject.Set<T>();
            }
            return tcObject.Set<T>().Where(expression) as IQueryable<T>;
        }


        /// <summary>
        /// 通过Lambda或查询单条或多条记录
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public async Task<IQueryable<T>> SearchAsync<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            if (expression == null)
            {
                return await Task.FromResult<IQueryable<T>>(tcObject.Set<T>());
            }
            return await Task.FromResult<IQueryable<T>>(tcObject.Set<T>().Where(expression) as IQueryable<T>);
        }

        /// <summary>
        /// 通过Lambda查询单条记录
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public T SearchFirstOrDefault<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (expression == null)
                {
                    return tc.Set<T>().FirstOrDefault();
                }
                return tc.Set<T>().Where(expression).FirstOrDefault();
            }
        }

        /// <summary>
        /// 通过Lambda查询单条记录
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public async Task<T> SearchFirstOrDefaultAsync<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (expression == null)
                {
                    return await Task.FromResult<T>(tc.Set<T>().FirstOrDefault());
                }
                return await Task.FromResult<T>(tc.Set<T>().Where(expression).FirstOrDefault());
            }
        }



        /// <summary>
        /// 通过SQL语句查询单条记录
        /// Lv
        /// 2018/02/26
        /// </summary>
        public T SearchFirstOrDefault<T>(string sql, MySqlParameter[] paramer = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (paramer != null)
                {
                    return tc.Database.SqlQuery<T>(sql, paramer).FirstOrDefault();
                }
                return tc.Database.SqlQuery<T>(sql).FirstOrDefault();
            }
        }

        public async Task<T> SearchFirstOrDefaultAsync<T>(string sql, MySqlParameter[] paramer = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (paramer != null)
                {
                    return await Task.FromResult<T>(tc.Database.SqlQuery<T>(sql, paramer).FirstOrDefault());
                }
                return await Task.FromResult<T>(tc.Database.SqlQuery<T>(sql).FirstOrDefault());
            }
        }


        /// <summary>
        /// 通过Lambda语句查询单条或多条List集合
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public List<T> SearchList<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (expression == null)
                {
                    return tc.Set<T>().ToList();
                }
                return tc.Set<T>().Where(expression).ToList();
            }
        }

        /// <summary>
        /// 通过Lambda语句查询单条或多条List集合
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public async Task<List<T>> SearchListAsync<T>(Expression<Func<T, bool>> expression = null) where T : BaseEntity
        {
            using (TC tc = new TC())
            {
                if (expression == null)
                {
                    return await Task.FromResult<List<T>>(tc.Set<T>().ToList());
                }
                return await Task.FromResult<List<T>>(tc.Set<T>().Where(expression).ToList());
            }
        }

        /// <summary>
        /// 通过SQL语句查询单条或多条List集合
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public List<T> SearchList<T>(string sql) where T : BaseEntity
        {
            return tcObject.Database.SqlQuery<T>(sql).ToList();
        }
        /// <summary>
        /// 通过SQL语句查询单条或多条List集合
        /// 条件可空
        /// Lv
        /// 2018/02/26
        /// </summary>
        public async Task<List<T>> SearchListAsync<T>(string sql) where T : BaseEntity
        {
            return await Task.FromResult<List<T>>(tcObject.Database.SqlQuery<T>(sql).ToList());
        }

        #endregion

        #region 更新

        /// <summary>
        /// 通过单模型更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// </summary>
        public int Update<T>(T t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Modified;
                return tc.SaveChanges();
            }
        }

        public async Task<int> UpdateAsync<T>(T t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                tc.Entry<T>(t).State = System.Data.Entity.EntityState.Modified;
                return await tc.SaveChangesAsync();
            }
        }

        /// <summary>
        /// 通过模型集合更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// </summary>
        public int Update<T>(IEnumerable<T> t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Modified;
                }
                return tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过模型集合更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// </summary>
        public async Task<int> UpdateAsync<T>(IEnumerable<T> t) where T : BaseEntity
        {
            if (t == null) { return 0; }
            using (TC tc = new TC())
            {
                foreach (var item in t)
                {
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Modified;
                }
                return await tc.SaveChangesAsync();
            }
        }

        /// <summary>
        /// 通过Lambda及Action更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// 不可用于过多行更新
        /// </summary>
        public int Update<T>(Expression<Func<T, bool>> expression, Action<T> action) where T : BaseEntity
        {
            if (expression == null || action == null)
            {
                throw new ArgumentNullException("expression , action ");
            }
            using (TC tc = new TC())
            {
                IEnumerable<T> _list = tc.Set<T>().Where(expression);
                foreach (var item in _list)
                {
                    action(item);
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Modified;
                }
                return  tc.SaveChanges();
            }
        }

        /// <summary>
        /// 通过Lambda及Action更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// 不可用于过多行更新
        /// </summary>
        public async Task<int> UpdateAsync<T>(Expression<Func<T, bool>> expression, Action<T> action) where T : BaseEntity
        {
            if (expression == null || action == null)
            {
                throw new ArgumentNullException("expression , action ");
            }
            using (TC tc = new TC())
            {
                IEnumerable<T> _list = tc.Set<T>().Where(expression);
                foreach (var item in _list)
                {
                    action(item);
                    tc.Entry<T>(item).State = System.Data.Entity.EntityState.Modified;
                }
                return await tc.SaveChangesAsync();
            }
        }

        /// <summary>
        /// 通过SQL语句更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// </summary>
        public int Update(string sql)
        {
            return this.ExecuteSql(sql);
        }

        /// <summary>
        /// 通过SQL语句更新数据库内容
        /// Lv
        /// 2018/02/26
        /// 返回受影响行数
        /// </summary>
        public async Task<int> UpdateAsync(string sql, MySqlParameter[] parameters = null)
        {
            return await this.ExecuteSqlAsync(sql, parameters);
        }

        [Obsolete("未实现更新方法，始终返回 0 ")]
        public int UpdateAdoptSql<T>(T t) where T : BaseEntity
        {
            return 0;
        }

        #endregion

        #region SQL相关

        /// <summary>
        /// 检查表 是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>True:存在,False:未查询到或不存在</returns>
        public bool TableExist(string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName)) { return false; }
            var T_Sql = string.Format("SHOW TABLES LIKE '%{0}%'", tableName);
            return tcObject.Database.SqlQuery<string>(T_Sql).First() != null;
        }

        /// <summary>
        /// 执行存储过程
        /// 0.1
        /// 2018/02/26
        /// </summary>
        public T ExecuteStoredProcedure<T>(string procedureName, MySqlParameter[] parameter = null) where T : BaseEntity
        {
            Object t_obj = null;
            ExecuteSql(procedureName, parameter, true, p =>
            {
                while (p.Read())
                {
                    t_obj = ReaderObject<T>(p);
                }
            });
            return t_obj == null ? default(T) : (T)t_obj;
        }

        #endregion

        #region 执行SQL

        /// <summary>
        /// 执行SQL语句返回受影响行数
        /// 0.1
        /// Lv
        /// 2018/02/10
        /// </summary>
        private int ExecuteSql(string sql, MySqlParameter[] parameters = null)
        {
            string _connectionString = tcObject.ConnectionString;
            if (!string.IsNullOrWhiteSpace(_connectionString) && !string.IsNullOrWhiteSpace(sql))
            {
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandText = sql;
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            return 0;
        }

        private async Task<int> ExecuteSqlAsync(string sql, MySqlParameter[] parameters = null)
        {
            string _connectionString = tcObject.ConnectionString;
            if (!string.IsNullOrWhiteSpace(_connectionString) && !string.IsNullOrWhiteSpace(sql))
            {
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        await conn.OpenAsync();
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandText = sql;
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        return await cmd.ExecuteNonQueryAsync();
                    }
                }
            }
            return 0;
        }

        /// <summary>
        /// 执行SQL语句
        /// 执行存储过程等
        /// 0.1
        /// Lv
        /// 2018/02/10
        /// </summary>
        private void ExecuteSql(string text, MySqlParameter[] parameter, bool isProcedure, Action<MySqlDataReader> action)
        {

            string _connectionString = tcObject.ConnectionString;
            if (string.IsNullOrWhiteSpace(_connectionString))
            {
                throw new ArgumentNullException("ConnectionString Is Null Or Empty");
            }
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;
                    cmd.CommandText = text;
                    if (parameter != null)
                    {
                        foreach (MySqlParameter item in parameter)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }
                    MySqlDataReader reader = cmd.ExecuteReader();
                    action(reader);
                }
            }
        }

        #endregion

        #region 相关
        private object ReaderObject<T>(MySqlDataReader reader)
        {
            object result = null;
            if (typeof(bool) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(bool));
            }
            else if (typeof(byte) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(byte));
            }
            else if (typeof(char) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(char));
            }
            else if (typeof(DateTime) == typeof(T) || typeof(DateTime?) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(DateTimeOffset));
            }
            else if (typeof(decimal) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(decimal));
            }
            else if (typeof(double) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(double));
            }
            else if (typeof(float) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(float));
            }
            else if (typeof(Guid) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(Guid));
            }
            else if (typeof(short) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(short));
            }
            else if (typeof(int) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(int));
            }
            else if (typeof(string) == typeof(T))
            {
                result = ReaderElement(reader, 0, typeof(string));
            }
            else
            {
                result = System.Activator.CreateInstance<T>();
                int i = 0;
                foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(result))
                {
                    try
                    {
                        if (reader.GetSchemaTable().Select("ColumnName='" + property.DisplayName + "'").Length > 0)
                        {
                            i = reader.GetOrdinal(property.DisplayName);
                            if (property.PropertyType.Name == "Int32")
                            {
                                int o = Convert.ToInt32(i);
                                property.SetValue(result, ReaderElement(reader, o, property.PropertyType));
                            }
                            else
                            {
                                property.SetValue(result, ReaderElement(reader, i, property.PropertyType));
                            }

                        }
                    }
                    catch (Exception ex)
                    {
                        throw new ArgumentException("绑定属性【" + property.Name + "】发生异常:" + ex.Message);
                    }
                }
            }
            return result;
        }

        private object ReaderElement(MySqlDataReader reader, int count, Type type)
        {
            if (typeof(bool) == type)
            {
                return reader.GetBoolean(count);
            }
            else if (typeof(byte) == type)
            {
                return reader.GetByte(count);
            }
            else if (typeof(char) == type)
            {
                return reader.GetChar(count);
            }
            else if (typeof(DateTime) == type || typeof(DateTime?) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new DateTime?();
                }
                return ra;
                //return reader.GetDateTime(count);
            }
            //else if (typeof(DateTimeOffset) == type)
            //{
            //    return reader.GetDateTimeOffset(count);
            //}
            else if (typeof(decimal) == type || typeof(int?) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new decimal?();
                }
                return ra;
                //return reader.GetDecimal(count);
            }
            else if (typeof(double) == type)
            {
                return reader.GetDouble(count);
            }
            else if (typeof(float) == type)
            {
                return reader.GetFloat(count);
            }
            else if (typeof(Guid) == type)
            {
                return reader.GetGuid(count);
            }
            else if (typeof(short) == type)
            {
                return reader.GetInt16(count);
            }
            else if (typeof(int) == type || typeof(int?) == type)
            {
                //return reader.GetInt32(count);
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new int?();
                }
                return ra;
            }
            else if (typeof(long) == type)
            {
                object ra = reader[count];
                if (ra is DBNull)
                {
                    return new long?();
                }
                return Convert.ToInt64(ra);
            }
            else
            {
                return reader.IsDBNull(count) ? null : reader.GetString(count);
            }
        }

        #endregion
    }
}
